package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.SaleArea;
import com.sl.au.entity.Section;
import com.sl.au.entity.SectionArea;
import com.sl.au.entity.User;

@Transactional(readOnly = true)
public interface SectionRepository extends JpaRepository<Section, String> {
	@Query(" select s from Section s where s.isAbandon=0 and (s.sectionName like '%'+?1+'%' or s.superior like '%'+?1+'%')")
	Page<Section> findSection(String key, Pageable pageable);
	
	@Query(" select count(*) from Section s where s.isAbandon=0 and (s.sectionName like '%'+?1+'%' or s.superior like '%'+?1+'%') ")
	long getCount(String key);
	
	Section findByChief(User chief);
	

	
	 /*
     * 部门与科室的唯一验证
     */
	@Query("select count(*) from Section s where s.isAbandon = 0 and s.sectionArea = ?1 ")
	long getRearea(String sectionArea);
	
	/**根据部门科室找到记录
	 * @param superiorArea
	 * @return
	 */
	Section findBySectionArea(String sectionArea);
	
	
	/*
	 * 获得科室配置的片区
	 */
	@Query(" select s from SectionArea s where s.section.id=?1")
	Page<SectionArea> findSectionArea(String sectionId, Pageable pageable);
	
	@Query(" select count(*) from SectionArea s where s.section.id=?1")
	long getSACount(String sectionId);
	
	/*
	 * 获得没有被科室配置的片区
	 */
	@Query(" select s from SaleArea s where (s.superior=?2) and (s.isAbandon = 0) and (s.id not in (select a.saleArea.id from SectionArea a where a.section.id=?1))")
	Page<SaleArea> findSaleArea(String sectionId, String superior,Pageable pageable);
	
	@Query(" select count(*) from SaleArea s where (s.superior=?2) and (s.isAbandon = 0) and (s not in (select a.saleArea.id from SectionArea a where a.section.id=?1))")
	long getNoACount(String sectionId,String superior);

	@Query(" select s from SectionArea s where (s.saleArea.superior=?1) and (s.saleArea.areaName like '%'+?2+'%')")
	List<SectionArea> findSectionAreaOne(String superior, String areaName);
	
	
}
